﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{
    //present
    public class ProductGateway:DbGateway
    {
        //Generic aGeneric=new Generic();
        //Therapeutic aTherapeutic=new Therapeutic();
        //Form aForm=new Form();
        //Group aGroup=new Group();
        Company aCompany = new Company();
        //Units aUnits=new Units();
        Store aStore=new Store();

        public string SaveProduct(Product aProduct)
        {
            try
            {             
                string query = String.Format("INSERT INTO PosProduct VALUES(@productName,@tradePrice,@flatPrice,@vat,@mrp,@discountPer,@packSize,@reOrderQty,@genericId,@therapeuticId,@formId,@groupId,@companyId,@unitId,@storeId,@rackId,@shelfId,@paymentMode,@createBy,@createDate)");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = query;
                CommandObj.Parameters.Add("@productName", SqlDbType.VarChar);
                CommandObj.Parameters["@productName"].Value = aProduct.ProductBrandName;
                CommandObj.Parameters.Add("@tradePrice", SqlDbType.Decimal);
                CommandObj.Parameters["@tradePrice"].Value =aProduct.TradePrice;
                CommandObj.Parameters.Add("@flatPrice", SqlDbType.Decimal);
                CommandObj.Parameters["@flatPrice"].Value = aProduct.FlatPrice;
                CommandObj.Parameters.Add("@vat", SqlDbType.Decimal);
                CommandObj.Parameters["@vat"].Value = aProduct.Vat;
                CommandObj.Parameters.Add("@mrp", SqlDbType.Decimal);
                CommandObj.Parameters["@mrp"].Value = aProduct.Mrp;
                CommandObj.Parameters.Add("@discountPer", SqlDbType.Decimal);
                CommandObj.Parameters["@discountPer"].Value = aProduct.DiscountPer;
                CommandObj.Parameters.Add("@packSize", SqlDbType.Decimal);
                //CommandObj.Parameters["@packSize"].Value = aProduct.PackSize;
                CommandObj.Parameters.Add("@reOrderQty", SqlDbType.Int);
                CommandObj.Parameters["@reOrderQty"].Value = aProduct.ReOrderQty;
                CommandObj.Parameters.Add("@genericId", SqlDbType.Int);
                //CommandObj.Parameters["@genericId"].Value = aProduct.AGeneric.GenericId;
                CommandObj.Parameters.Add("@therapeuticId", SqlDbType.Int);
                //CommandObj.Parameters["@therapeuticId"].Value = aProduct.ATherapeutic.TherapeuticId;
                CommandObj.Parameters.Add("@formId", SqlDbType.Int);
               // CommandObj.Parameters["@formId"].Value = aProduct.AForm.FormId;
                CommandObj.Parameters.Add("@groupId", SqlDbType.Int);
                //CommandObj.Parameters["@groupId"].Value = aProduct.AGroup.GroupId;
                CommandObj.Parameters.Add("@companyId", SqlDbType.Int);
                CommandObj.Parameters["@companyId"].Value = aProduct.ACompany.CompanyId;
                CommandObj.Parameters.Add("@unitId", SqlDbType.Int);
                //CommandObj.Parameters["@unitId"].Value = aProduct.AUnits.UnitId;
                CommandObj.Parameters.Add("@storeId", SqlDbType.Int);
                //CommandObj.Parameters["@storeId"].Value = aProduct.AStore.StoreId;
                CommandObj.Parameters.Add("@rackId", SqlDbType.VarChar);
               // CommandObj.Parameters["@rackId"].Value = aProduct.ARack.RackId;
                CommandObj.Parameters.Add("@shelfId", SqlDbType.VarChar);
                //CommandObj.Parameters["@shelfId"].Value = aProduct.AShelf.ShelfId;
                CommandObj.Parameters.Add("@paymentMode", SqlDbType.VarChar);
                //CommandObj.Parameters["@paymentMode"].Value = aProduct.PaymentMode;
                CommandObj.Parameters.Add("@createBy", SqlDbType.VarChar);
                CommandObj.Parameters["@createBy"].Value = aProduct.CreateBy;
                CommandObj.Parameters.Add("@createDate", SqlDbType.DateTime);
                CommandObj.Parameters["@createDate"].Value = aProduct.CreateDate;
                CommandObj.ExecuteNonQuery();
                
                //CommandObj.Parameters.Add("@editBy", SqlDbType.VarChar);
                //CommandObj.Parameters["@editBy"].Value = aProduct.EditBy;
                //CommandObj.Parameters.Add("@editDate", SqlDbType.DateTime);
                //CommandObj.Parameters["@editDate"].Value = aProduct.EditBy;            
            }
            catch (Exception ex)
            {
                throw new Exception("Supplier couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "successful";
        }

        public List<Product> GetAllProduct()
        {
            List<Product> products = new List<Product>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosProduct");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Product aProduct = new Product();
                    aProduct.ProductId = Convert.ToInt32(reader["ProductId"]);
                    aProduct.ProductBrandName = reader["ProductBrandName"].ToString();
                    products.Add(aProduct);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Product loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return products;
        }
        public Product FindProduct(int productId)
        {
            try
            {
                string search = String.Format("Select * FROM PosProduct WHERE ProductId=@ProductId");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@ProductId", SqlDbType.Int);
                CommandObj.Parameters["@ProductId"].Value = productId;
                CommandObj.CommandText = search;
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                Product Product = new Product();
                if (sqlDataReader != null)
                {
                    while (sqlDataReader.Read())
                    {
                        Product.ProductId = Convert.ToInt32(sqlDataReader["ProductId"]);
                        Product.ProductBrandName = sqlDataReader["ProductName"].ToString();
                        return Product;
                    }
                }
                return null;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found Product", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public Product GetUnitPrice(int categoryId, string productName)
        {
            decimal tradePrice = 0;

            try
            {
                string select = String.Format(
                    "SELECT * FROM PosProduct Where categoryId='{0}' and productBrandName='{1}'", categoryId, productName);
                CommandObj.Connection = ConnectionObj;
                CommandObj.CommandText = select;
                ConnectionObj.Open();
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                Product product = null;
                while (sqlDataReader.Read())
                {
                    product = new Product();
                    product.ProductId = Convert.ToInt32(sqlDataReader[0]);
                    product.TradePrice = Convert.ToDecimal(sqlDataReader["tradePrice"]);
                    product.Vat = Convert.ToDecimal(sqlDataReader["vat"]);
                    product.Mrp = Convert.ToDecimal(sqlDataReader["mrp"]);
                }
                return product;
            }
            catch (Exception exception)
            {

                throw new Exception("Couldn't Found Unit Price For this Product.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }


        public string DeleteProductById(int productId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosProduct WHERE productId='{0}'", productId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "Product Id does not found";
            }
        }

    } 
}
